﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.SqlClient;

namespace Hired
{
    /*  A question posted in the Q&A section of the website.    */
    public class DBQuestion
    {
        public List<string> categoryList = new List<string> { "hey" };

        public static bool AddQuestion(Question question)
        {
            try
            {
                question.QuestionID = "tempvalue";
                using (hireddbEntities db = new hireddbEntities())
                {
                    db.AddToQuestions(question);
                    db.SaveChanges();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static Question RetrieveQuestion(string questionID, bool answers)
        {
            using (hireddbEntities db = new hireddbEntities())
            {
                if (answers)
                {
                    return
                        (from q in db.Questions.Include("Answers")
                         where q.QuestionID == questionID
                         select q).SingleOrDefault();
                }
                else
                {
                    return
                        (from q in db.Questions
                         where q.QuestionID == questionID
                         select q).SingleOrDefault();
                }
            }
        }

        public static List<object> RetrieveTopQuestionsByCategory(string category)
        {
            List<object> questionsList = new List<object>();
            using (hireddbEntities db = new hireddbEntities())
            {
                var questions =
                    (from q in db.Questions
                     where q.Category == category
                     orderby q.Timestamp descending
                     select q).ToList<Question>();
                if (questions.Count != 0)
                {
                    List<string> usernameList = new List<string>();
                    foreach (Question q in questions)
                    {
                        object posterAccount = DBAccount.RetrieveAccountByID(q.UserID);
                        string accountType = DBAccount.RetrieveAccountTypeByAccount(posterAccount);

                        switch (accountType)
                        {
                            case "AdminStaffAccount":
                                usernameList.Add(((AdminStaffAccount)(posterAccount)).Username);
                                break;
                            case "EmployerPassport":
                                usernameList.Add(((EmployerPassport)(posterAccount)).Username);
                                break;
                            case "JobseekerAccount":
                                usernameList.Add(((JobseekerAccount)(posterAccount)).Username);
                                break;
                            default:
                                usernameList.Add("unknown");
                                break;
                        }
                    }
                    questionsList =
                        (from q in questions
                         from u in usernameList
                         select new { QuestionID = q.QuestionID, Username = u, Title = q.Title, Timestamp = q.Timestamp }).Distinct().ToList<object>(); ;
                }
            }
            return questionsList;
        }

        public static List<object> RetrieveLastAnsweredQuestionsByCategory(string category)
        {
            List<object> questionsList = new List<object>();
            using (hireddbEntities db = new hireddbEntities())
            {
                var questions =
                    (from q in db.Questions
                     where q.Category == category
                     orderby q.LastAnswer descending
                     select q);
                if (questions.Count() != 0)
                {
                    List<string> posterList = new List<string>();
                    List<string> answererList = new List<string>();
                    foreach (Question q in questions)
                    {
                        object account = DBAccount.RetrieveAccountByID(q.UserID);
                        string accountType = DBAccount.RetrieveAccountTypeByAccount(account);

                        switch (accountType)
                        {
                            case "AdminStaffAccount":
                                posterList.Add(((AdminStaffAccount)(account)).Username);
                                break;
                            case "EmployerPassport":
                                posterList.Add(((EmployerPassport)(account)).Username);
                                break;
                            case "JobseekerAccount":
                                posterList.Add(((JobseekerAccount)(account)).Username);
                                break;
                            default:
                                posterList.Add("unknown");
                                break;
                        }

                        if (q.LastPoster != null)
                        {
                            account = DBAccount.RetrieveAccountByID(q.LastPoster);
                            accountType = DBAccount.RetrieveAccountTypeByAccount(account);

                            switch (accountType)
                            {
                                case "AdminStaffAccount":
                                    answererList.Add(((AdminStaffAccount)(account)).Username);
                                    break;
                                case "EmployerPassport":
                                    answererList.Add(((EmployerPassport)(account)).Username);
                                    break;
                                case "JobseekerAccount":
                                    answererList.Add(((JobseekerAccount)(account)).Username);
                                    break;
                                default:
                                    answererList.Add("unknown");
                                    break;
                            }
                        }
                        else
                        {
                            answererList.Add(null);
                        }
                    }
                    questionsList =
                            (from q in questions
                             from p in posterList
                             from a in answererList
                             select new { QuestionID = q.QuestionID, Username = p, Title = q.Title, LastAnswer = q.LastAnswer, LastPoster = a }).Distinct().ToList<object>(); ;
                }
            }
            return questionsList;
        }

        public static bool UpdateQuestion(Question que)
        {
            try
            {
                using (hireddbEntities db = new hireddbEntities())
                {
                    Question questionToUpdate = RetrieveQuestion(que.QuestionID, false);
                    db.Attach(questionToUpdate);
                    questionToUpdate.Contents = que.Contents;
                    questionToUpdate.Timestamp = que.Timestamp;
                    questionToUpdate.Title = que.Title;
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool DeleteQuestion(Question que)
        {
            try
            {
                using (hireddbEntities db = new hireddbEntities())
                {
                    Question questionToDelete = RetrieveQuestion(que.QuestionID, false);
                    db.Attach(questionToDelete);
                    db.Questions.DeleteObject(questionToDelete);
                    db.SaveChanges();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public static bool DeleteQuestion(string id)
        {
            try
            {
                using (hireddbEntities db = new hireddbEntities())
                {
                    Question questionToDelete = RetrieveQuestion(id, false);
                    db.Attach(questionToDelete);
                    db.Questions.DeleteObject(questionToDelete);
                    db.SaveChanges();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public static List<object> searchQuestion(string searchQuery, string category)
        {
            List<object> questionsList = new List<object>();
            searchQuery = searchQuery.ToUpper();
            using (hireddbEntities db = new hireddbEntities())
            {
                var questions =
                    from q in db.Questions
                    where
                    q.Title.ToUpper().Contains(searchQuery)
                    || q.Contents.ToUpper().Contains(searchQuery)
                    || q.Answers.Any(a => a.Contents.ToUpper().Contains(searchQuery))
                    select q;
                if (category != "All categories")
                {
                    questions = questions.Where(q => q.Category == category);
                }
                if (questions.Count() != 0)
                {
                    List<string> posterList = new List<string>();
                    List<string> answererList = new List<string>();
                    foreach (Question q in questions)
                    {
                        object account = DBAccount.RetrieveAccountByID(q.UserID);
                        string accountType = DBAccount.RetrieveAccountTypeByAccount(account);

                        switch (accountType)
                        {
                            case "AdminStaffAccount":
                                posterList.Add(((AdminStaffAccount)(account)).Username);
                                break;
                            case "EmployerPassport":
                                posterList.Add(((EmployerPassport)(account)).Username);
                                break;
                            case "JobseekerAccount":
                                posterList.Add(((JobseekerAccount)(account)).Username);
                                break;
                            default:
                                posterList.Add("unknown");
                                break;
                        }
                        if (q.LastPoster != null)
                        {
                            account = DBAccount.RetrieveAccountByID(q.LastPoster);
                            accountType = DBAccount.RetrieveAccountTypeByAccount(account);

                            switch (accountType)
                            {
                                case "AdminStaffAccount":
                                    answererList.Add(((AdminStaffAccount)(account)).Username);
                                    break;
                                case "EmployerPassport":
                                    answererList.Add(((EmployerPassport)(account)).Username);
                                    break;
                                case "JobseekerAccount":
                                    answererList.Add(((JobseekerAccount)(account)).Username);
                                    break;
                                default:
                                    answererList.Add("unknown");
                                    break;
                            }
                        }
                        else
                        {
                            answererList.Add(null);
                        }
                    }
                    questionsList =
                            (from q in questions
                             from p in posterList
                             from a in answererList
                             select new { QuestionID = q.QuestionID, Username = p, Title = q.Title, LastAnswer = q.LastAnswer, LastPoster = a }).ToList<object>(); ;
                }
            }
            return questionsList;
        }
    }
}